package org.hyx.topic1;

import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

public class JdbcTemplateTest {
    //定义配置文件路径（绝对路径）

    //定义配置文件路径（相对路径）

    //加载配置文件
    ApplicationContext applicationContext = new ClassPathXmlApplicationContext("JdbcTemplateBeans.xml");
    //获取jdbcTemplate实例
    JdbcTemplate jdTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
    StudentDao stuDao = (StudentDao) applicationContext.getBean("studentDao");

    /**
     * 使用execute方法建表
     */
    @Test
    public void createTableTest() {
        String sql = "CREATE TABLE `users` ("
                + "  `id` int(10) NOT NULL,  `num` varchar(15) NOT NULL,"
                + "  `userName` varchar(50) NOT NULL,"
                + "  `password` varchar(50) NOT NULL,"
                + "  `sex` varchar(10) NOT NULL,"
                + "  `regDate` varchar(20) DEFAULT NULL,"
                + "  PRIMARY KEY (`id`,`num`)"
                + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        //使用execute方法执行SQL语句，创建数据表
        jdTemplate.execute(sql);
    }

    /**
     * 添加学生
     */
    @Test
    public void addStudentTest() {
        Student stu = new Student();
        //向stu对象添加属性值：自己的学号、姓名和性别
        stu.setId(1).setNum("1940129123").setUserName("hyx").setSex("男").setPassword("666").setRegDate(DateTimeFormatter.ofPattern("yyyy-MM-dd").format(LocalDateTime.now()));
        //调用addStudent方法，获取返回结果
        int flag = stuDao.addStudent(stu);
        if (flag == 1) {
            System.out.println("注册成功");
        } else {
            System.out.println("注册失败");
        }
    }

    /**
     * 更新学生数据
     */
    @Test
    public void updateStudentTest() {
        Student stu = stuDao.findStudentByNum("1940129123");
        System.out.printf("修改前：%s",stu.toString());
        //Student stu = new Student();
        //向stu对象修改属性值：修改密码为自己的学号
        stu.setPassword("1940129123");
        //调用StudentDao中的updateStudent()方法执行更新
        int flag = stuDao.updateStudent(stu);
        if(flag == 1){
            System.out.println("修改成功");
            System.out.printf("修改后：%s",stuDao.findStudentByNum("1940129123").toString());
        }else{
            System.out.println("修改失败");
        }
    }

    @Test
    public void deleteStudentByNum() {
        //调用deleteStudentByNum()方法，获取返回结果
        int flag =stuDao.deleteStudentByNum("1940129123");
        if (flag == 1) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
    }

    /**
     * 根据学号num查找学生
     */
    @Test
    public void findStudentByNumTest() {
        //获取stuDao实例
        StudentDao stuDao = (StudentDao) applicationContext.getBean("studentDao");
        //调用findStudentById方法，获取Student对象
        Student stu =stuDao.findStudentByNum("1940129123");
        //输出查询结果
        System.out.println(stu);
    }

    /**
     * 查找所有学生
     */
    @Test
    public void findAllStudentTest() {
        //获取stuDao实例
        //StudentDao stuDao = (StudentDao) applicationContext.getBean("studentDao");
        //调用findAllStudent方法，获取Student对象集合
        List<Student> stus = stuDao.findAllStudents();
        //循环输出集合中对象
        stus.forEach(System.out::println);
    }
}
